"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : dolphin
Case Name   : char(n),character(n),nchar(n)类型字符串后包含空格结合hashjoin方式及逻辑运算符条件验证
Create At   : 2022/12/12
@zou_jialiang0501129488
Description :
    1、创建b库并查询插件
    2、设置join方式为hashjoin
    3、建表插数据并进行join查询
    4、清理环境
Expect      :
    1、成功
    2、自动加载成功
    3、成功
    4、成功
History     :
    modified by @zou_jialiang050,2023.03.31，优化断言信息
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class CompatibilityTest001(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.constant = Constant()

    def test_string(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        text = '-----step1:创建兼容b库的数据库;expect:成功-----'
        self.log.info(text)
        sql_cmd = f"drop database if exists db_string_case43;" \
                  f"create database db_string_case43 dbcompatibility ='B';"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd, dbname='postgres')
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_res,
                      '执行失败' + text)
        sql_cmd = "select extname from pg_extension where extname ='dolphin';"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname='db_string_case43')
        self.log.info(sql_res)
        self.assertIn('dolphin', sql_res, '插件加载失败' + text)

        text = '-----step2:设置join方式为hashjoin;expect:成功-----'
        self.log.info(text)
        guc_res = self.sh_primary.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG,
            'string_hash_compatible=on', get_detail=True)
        self.assertIn(self.constant.GSGUC_SUCCESS_MSG, guc_res)

        restart_res = self.sh_primary.restart_db_cluster(get_detail=True)
        self.log.info(restart_res)
        stat_res = self.sh_primary.get_db_cluster_status()
        self.assertTrue("Degraded" in stat_res or "Normal" in stat_res,
                        f'执行失败:{text}')

        sql_cmd = f'set enable_mergejoin to off;' \
                  f'set enable_nestloop to off;' \
                  f'set enable_hashjoin to on;'
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname='db_string_case43')
        self.log.info(sql_res)
        self.assertEqual(3, sql_res.count('SET'), '参数设置失败')

        text = '-----step3:建表插数据并进行join查询;expect:成功-----'
        self.log.info(text)
        sql_cmd = f"drop table if exists t_string_00043 cascade;" \
                  f"drop table if exists t_string_00043_1 cascade;" \
                  f"create table t_string_00043(" \
                  f"c_1 char(10),c_2 character(10),c_3 nchar(10));" \
                  f"create table t_string_00043_1(" \
                  f"c_1 char(10),c_2 character(10),c_3 nchar(10));" \
                  f"insert into t_string_00043 values" \
                  f"('1aA干','1aA干','1aA干')," \
                  f"('1aA干 ','1aA干 ','1aA干 '),('1aA干 ','1aA干 ','1aA干 ');" \
                  f"insert into t_string_00043_1 values" \
                  f"('1aA干','1aA干','1aA干')," \
                  f"('1aA干 ','1aA干 ','1aA干 '),('1aA干 ','1aA干 ','1aA干 ');"
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname='db_string_case43')
        self.log.info(sql_res)
        self.assertEqual(2, sql_res.count('INSERT 0 3'), '数据插入失败')

        sql_cmd = f"select * from t_string_00043 inner join t_string_00043_1 " \
                  f"on t_string_00043.c_1=t_string_00043_1.c_1;"
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname='db_string_case43')
        self.log.info(sql_res)
        self.assertIn('(9 rows)', sql_res, '查询失败')

        sql_cmd = "select * from t_string_00043 inner join t_string_00043_1 " \
                  "on t_string_00043.c_1!=t_string_00043_1.c_1;"
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname='db_string_case43')
        self.log.info(sql_res)
        self.assertIn('(0 rows)', sql_res, '查询失败')

        sql_cmd = "select * from t_string_00043 inner join t_string_00043_1 " \
                  "on t_string_00043.c_2=t_string_00043_1.c_2;"
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname='db_string_case43')
        self.log.info(sql_res)
        self.assertIn('(9 rows)', sql_res, '查询失败')

        sql_cmd = "select * from t_string_00043 inner join t_string_00043_1 " \
                  "on t_string_00043.c_2!=t_string_00043_1.c_2;"
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname='db_string_case43')
        self.log.info(sql_res)
        self.assertIn('(0 rows)', sql_res, '查询失败')

        sql_cmd = "select * from t_string_00043 inner join t_string_00043_1 " \
                  "on t_string_00043.c_3=t_string_00043_1.c_3;"
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname='db_string_case43')
        self.log.info(sql_res)
        self.assertIn('(9 rows)', sql_res, '查询失败')

        sql_cmd = "select * from t_string_00043 inner join t_string_00043_1 " \
                  "on t_string_00043.c_3!=t_string_00043_1.c_3;"
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname='db_string_case43')
        self.log.info(sql_res)
        self.assertIn('(0 rows)', sql_res, '查询失败')

    def tearDown(self):
        self.log.info('-----step4:清理环境;expect:成功-----')
        guc_res = self.sh_primary.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG,
            'string_hash_compatible=on', get_detail=True)
        restart_res = self.sh_primary.restart_db_cluster(get_detail=True)
        self.log.info(restart_res)
        stat_res = self.sh_primary.get_db_cluster_status()
        sql_cmd = f'drop database if exists db_string_case43;'
        sql_res = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertTrue("Degraded" in stat_res or "Normal" in stat_res,
                        '重启集群失败')
        self.assertIn(self.constant.GSGUC_SUCCESS_MSG, guc_res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS,
                      sql_res, '参数设置失败')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
